package dao;

import model.Product;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class DepartmentDao {

    public static void main(String[] args) throws Exception {
        removeEmploy("62");
//queryEmployee();
    }

    public static void removeEmploy(String pid) throws Exception {
// 1.在工程中导入mysql驱动jar包
        // 2.注册Driver驱动类  包名+类名
        Class.forName("com.mysql.cj.jdbc.Driver");
        // 3.DriverManager 驱动管理类
        //通过驱动管理类建立和制定mysql服务器的连接，生成一个连接对象
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/company", "root", "root");
        // 4.获取执行平台
        Statement statement = connection.createStatement();
        // 5.执行sql语句
        String str = "delete from product where product.pid =" + pid;
        //影响数据库表的行数
        int i = statement.executeUpdate(str);//对于数据库来说，增删改都是更新操作 executeUpdate(sql)
        if (i < 1) {
            System.out.println("删除失败");
        } else {
            System.out.println("删除成功");
        }
        //遵从先打开后关闭
        statement.close();
        connection.close();
    }

    public static void queryEmployee() throws Exception {
        // 1.在工程中导入mysql驱动jar包
        // 2.注册Driver驱动类  包名+类名
        Class.forName("com.mysql.cj.jdbc.Driver");
        // 3.DriverManager 驱动管理类
        //通过驱动管理类建立和制定mysql服务器的连接，生成一个连接对象
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/company", "root", "root");
        // 4.获取执行平台
        Statement statement = connection.createStatement();
        // 5.执行sql语句
        String str = "select * from product where product.price between 1000 and 11000";

        //set结果集  集合
        ResultSet resultSet = statement.executeQuery(str);
        List<Product> productList = new ArrayList<>();
        while (resultSet.next()) {  //判断是否有下一条记录值
            //从resultSet取出结果值
            int pid = resultSet.getInt("pid");//columnTndex 列的索引
            String pname = resultSet.getString("pname");
            int price = resultSet.getInt("price");
//            System.out.println(pid + "+++++" + pname + "-------" + price);
            productList.add(new Product(pid, pname, price));
        }

        System.out.println(productList);
        // 7.释放资源，关闭打开的流
        resultSet.close();
        statement.close();
        connection.close();
    }


}